﻿using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace CommonUtils
{
    /// <summary>
    /// ADO.Net Base
    /// </summary>
    public abstract partial class DbBase
    {
        #region Identifier
        /// <summary>
        /// 字段开始标识
        /// </summary>
        protected virtual char FieldStartIdentifier { get; } = '[';

        /// <summary>
        /// 字段结束标识
        /// </summary>
        protected virtual char FieldEndIdentifier { get; } = ']';

        /// <summary>
        /// 参数标识
        /// </summary>
        protected virtual char ParameterIdentifier { get; } = '@';
        #endregion

        /*
        [
          {
            "CollectionName": "Databases",
            "NumberOfRestrictions": 1,
            "NumberOfIdentifierParts": 1
          },
          {
            "CollectionName": "Tables",
            "NumberOfRestrictions": 4,
            "NumberOfIdentifierParts": 2
          },
          {
            "CollectionName": "Columns",
            "NumberOfRestrictions": 4,
            "NumberOfIdentifierParts": 4
          },
          {
            "CollectionName": "Users",
            "NumberOfRestrictions": 1,
            "NumberOfIdentifierParts": 1
          }
          ...
        ]
        */
        private DataTable GetEngineSchema(string collectionName = null)
        {
            DataTable table;
            var connection = GetConnection();
            connection.Open();
            if (string.IsNullOrEmpty(collectionName))
                table = connection.GetSchema();
            else
                table = connection.GetSchema(collectionName);
            connection.Close();
            return table;
        }

        /*
        [
          {
            "CATALOG_NAME": "def",
            "database_name": "mysql",
            "DEFAULT_CHARACTER_SET_NAME": "utf8",
            "DEFAULT_COLLATION_NAME": "utf8_general_ci",
            "SQL_PATH": null
          }
          ...
        ] 
        */
        private DataTable GetDatabasesSchema()
        => GetEngineSchema("Databases");

        /*
        [
          {
            "TABLE_CATALOG": "def",
            "TABLE_SCHEMA": "test",
            "TABLE_NAME": "table2",
            "TABLE_TYPE": "BASE TABLE",
            "ENGINE": "InnoDB",
            "VERSION": 10,
            "ROW_FORMAT": "Dynamic",
            "TABLE_ROWS": 9,
            "AVG_ROW_LENGTH": 1820,
            "DATA_LENGTH": 16384,
            "MAX_DATA_LENGTH": 0,
            "INDEX_LENGTH": 0,
            "DATA_FREE": 0,
            "AUTO_INCREMENT": 10,
            "CREATE_TIME": "2020-03-12T15:25:10",
            "UPDATE_TIME": null,
            "CHECK_TIME": null,
            "TABLE_COLLATION": "utf8_general_ci",
            "CHECKSUM": null,
            "CREATE_OPTIONS": "",
            "TABLE_COMMENT": "我是注释"
          }
          ...
        ]
        */
        private DataTable GetTablesSchema()
        => GetEngineSchema("Tables");

        /// <summary>
        /// 获取所有字段架构 包含注释 数据类型说明为数据库类型,字符串形式
        /// </summary>
        /*
        [
            {
              "TABLE_CATALOG": "def",
              "TABLE_SCHEMA": "test",
              "TABLE_NAME": "table1",
              "COLUMN_NAME": "字段 2",
              "ORDINAL_POSITION": 2,
              "COLUMN_DEFAULT": null,
              "IS_NULLABLE": "YES",
              "DATA_TYPE": "int",
              "CHARACTER_MAXIMUM_LENGTH": null,
              "NUMERIC_PRECISION": 10,
              "NUMERIC_SCALE": 0,
              "DATETIME_PRECISION": null,
              "CHARACTER_SET_NAME": null,
              "COLLATION_NAME": null,
              "COLUMN_TYPE": "int(11)",
              "COLUMN_KEY": "",
              "EXTRA": "",
              "PRIVILEGES": "select,insert,update,references",
              "COLUMN_COMMENT": "注释2",
              "GENERATION_EXPRESSION": ""
            }
            ...
        ] 
        */
        public DataTable GetColumnsSchema(string tableName = null)
        {
            var schema = GetEngineSchema("Columns");
            if (!string.IsNullOrEmpty(tableName))
                schema = schema.GetSelectedTable(2, tableName);
            return schema;
        }


        /// <summary>
        /// 系统表/临时表关键字 $'不可作为关键字，正常表名也包含
        /// </summary>
        private static string[] _tempTableNameKeys = { "MSys", "$'_", "$'Print_", "_xlnm", "$FilterDatabase" };

        /// <summary>
        /// 获取所有表名
        /// </summary>
        public string[] GetTableNames()
        {
            var schema = GetTablesSchema();
            var names = new List<string>();
            foreach (DataRow row in schema.Rows)
            {
                var name = row["TABLE_NAME"].ToString();
                if (!name.Contains(_tempTableNameKeys))
                    names.Add(name);
            }
            return names.ToArray();
        }

        public bool HasTable(string tableName)
        => GetTableNames().Contains(tableName);

        public bool TableExists(string tableName)
        => HasTable(tableName);

        private DbDataReader GetEmptyDataReader(string tableName)
        => GetDataReader(string.Format("select * from {0}{2}{1} where 0 = 1;", FieldStartIdentifier, FieldEndIdentifier, tableName));

        private DbDataReader GetEntireDataReader(string tableName)
        => GetDataReader(string.Format("select * from {0}{2}{1};", FieldStartIdentifier, FieldEndIdentifier, tableName));

        /// <summary>
        /// 获取Reader结构  ///包含 类型///  缺失 注释
        /// </summary>
        private DataTable GetReaderSchema(string tableName)
        {
            var reader = GetEmptyDataReader(tableName);
            var schema = reader.GetSchemaTable();//same as reader.GetColumnSchema()
            reader.Close();
            return schema;
        }

        public DataTable GetEmptyDataTable(string tableName)
        {
            var table = GetEmptyDataReader(tableName).GetDataTable();
            table.TableName = tableName;
            return table;
        }

        public DataTable GetEntireDataTable(string tableName)
        {
            var table = GetEntireDataReader(tableName).GetDataTable();
            table.TableName = tableName;
            return table;
        }

        public DataTable[] GetDataTables()
        {
            var tableNames = GetTableNames();
            var tables = new DataTable[tableNames.Length];
            tableNames.Length.For(index => tables[index] = GetEntireDataTable(tableNames[index]));
            return tables;
        }

        public List<ColumnSchema> GetColumnSchemas(string tableName)
        {
            var columnsSchema = GetColumnsSchema(tableName);
            var hasComment = columnsSchema.Columns.Contains("COLUMN_COMMENT");
            var list = new List<ColumnSchema>();
            for (int rowIndex = 0; rowIndex < columnsSchema.Rows.Count; rowIndex++)
            {
                var item = new ColumnSchema();
                item.Name = columnsSchema.Rows[rowIndex]["COLUMN_NAME"].ToString();
                if (hasComment)
                    item.Comment = columnsSchema.Rows[rowIndex]["COLUMN_COMMENT"].ToString();
                item.Nullable = columnsSchema.Rows[rowIndex]["IS_NULLABLE"].ToString() == "YES";

                var DbDataTypeIdentifier = columnsSchema.Rows[rowIndex]["DATA_TYPE"].ToString();
                item.DataType = CodeFactory.Identifier.GetTypeByDbIdentifier(DbDataTypeIdentifier);
                list.Add(item);
            }
            return list;
        }

        public TableSchema[] GetTablesSchemas(string tableName = null)
        {
            var tablesSchema = GetTablesSchema();
            if (!string.IsNullOrEmpty(tableName))
                tablesSchema = tablesSchema.GetSelectedTable("TABLE_NAME", tableName);

            var tables = new List<TableSchema>();
            foreach (DataRow row in tablesSchema.Rows)
            {
                var table = new TableSchema
                {
                    Name = row["TABLE_NAME"].ToString(),
                    Comment = row["TABLE_COMMENT"].ToString()
                };
                table.Columns = GetColumnSchemas(table.Name);
                tables.Add(table);
            }
            return tables.ToArray();
        }

        public TableSchema GetTableSchema(string tableName)
        => GetTablesSchemas(tableName).FirstOrDefault();

        public string[] GetColumnNames(string tableName)
        => GetColumnSchemas(tableName).Select(m => m.Name).ToArray();
    }
}
